﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BookManagermentSystem
{
    public partial class ReaderManagermentForm : Form
    {
        string connStr = "Server=127.0.0.1;Database=csharpbookdb;uid=root;pwd=123456;";

        public ReaderManagermentForm()
        {
            InitializeComponent();
        }

        private void btnBackMenu_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void ReaderManagermentForm_FormClosed(object sender, FormClosedEventArgs e)
        {
            MainMenuForm.getInstance().Show();
        }

        private void DataBind()
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "select rdID 读者编号, rdType 读者类别号, rdName 读者姓名, rdDept 读者单位, rdQQ 读者QQ, rdBorrowQty 已借书数量 from reader";
                    MySqlDataAdapter sda = new MySqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dgvReader.DataSource = ds.Tables[0];
                    tbRdID.DataBindings.Clear();
                    tbRdType.DataBindings.Clear();
                    tbRdName.DataBindings.Clear();
                    tbRdDept.DataBindings.Clear();
                    tbRdQQ.DataBindings.Clear();
                    tbRdBorrowQty.DataBindings.Clear();

                    tbRdID.DataBindings.Add("Text", ds.Tables[0], "读者编号");
                    tbRdType.DataBindings.Add("Text", ds.Tables[0], "读者类别号");
                    tbRdName.DataBindings.Add("Text", ds.Tables[0], "读者姓名");
                    tbRdDept.DataBindings.Add("Text", ds.Tables[0], "读者单位");
                    tbRdQQ.DataBindings.Add("Text", ds.Tables[0], "读者QQ");
                    tbRdBorrowQty.DataBindings.Add("Text", ds.Tables[0], "已借书数量");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void ReaderManagermentForm_Load(object sender, EventArgs e)
        {
            DataBind();
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "insert into reader values(@rdID, @rdType, @rdName, @rdDept, @rdQQ, @rdBorrowQty)";
                    comm.Parameters.AddWithValue("@rdID", tbRdID.Text);
                    comm.Parameters.AddWithValue("@rdType", tbRdType.Text);
                    comm.Parameters.AddWithValue("@rdName", tbRdName.Text);
                    comm.Parameters.AddWithValue("@rdDept", tbRdDept.Text);
                    comm.Parameters.AddWithValue("@rdQQ", tbRdQQ.Text);
                    comm.Parameters.AddWithValue("@rdBorrowQty", tbRdBorrowQty.Text);

                    comm.ExecuteNonQuery();
                    MessageBox.Show("添加成功");
                    DataBind();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("添加失败 " + ex.Message);
            }
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "select rdID 读者编号, rdType 读者类别号, rdName 读者姓名, rdDept 读者单位, rdQQ 读者QQ, rdBorrowQty 已借书数量 from reader where rdDept like CONCAT(@rdDept, '%')";
                    comm.Parameters.AddWithValue("@rdDept", tbRdDept.Text);
                    MySqlDataAdapter sda = new MySqlDataAdapter(comm);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    dgvReader.DataSource = ds.Tables[0];
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作数据库出错 " + ex.Message);
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
            DialogResult dr = MessageBox.Show("确定要删除吗?", "确定", messButton);

            if (dr == DialogResult.OK)
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(connStr))
                    {
                        conn.Open();
                        MySqlCommand comm = conn.CreateCommand();
                        comm.CommandText = "delete from reader where rdID = @rdID";
                        comm.Parameters.AddWithValue("@rdID", tbRdID.Text);
                        comm.ExecuteNonQuery();
                        MessageBox.Show("删除成功");
                        DataBind();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("删除失败 " + ex.Message);
                }
            }
        }

        private void btnAlter_Click(object sender, EventArgs e)
        {
            try
            {
                using (MySqlConnection conn = new MySqlConnection(connStr))
                {
                    conn.Open();
                    MySqlCommand comm = conn.CreateCommand();
                    comm.CommandText = "update reader set rdID = @rdID, rdType = @rdType, rdName = @rdName, rdDept = @rdDept, rdQQ = @rdQQ, rdBorrowQty = @rdBorrowQty where rdID = @rdID";
                    comm.Parameters.AddWithValue("@rdID", tbRdID.Text);
                    comm.Parameters.AddWithValue("@rdType", tbRdType.Text);
                    comm.Parameters.AddWithValue("@rdName", tbRdName.Text);
                    comm.Parameters.AddWithValue("@rdDept", tbRdDept.Text);
                    comm.Parameters.AddWithValue("@rdQQ", tbRdQQ.Text);
                    comm.Parameters.AddWithValue("@rdBorrowQty", tbRdBorrowQty.Text);
                    comm.ExecuteNonQuery();
                    MessageBox.Show("更新成功");
                    DataBind();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("修改失败 " + ex.Message);
            }
        }
    }
}
